package com.z100h.supplychain.utils;

import java.awt.Image;
import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.imageio.ImageIO;
import javax.servlet.ServletOutputStream;

import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

public class ExcelUtil {

	private static double count = 0.00;

	private static double sum_day = 0.00;

	// private static Map<String, String> uploadMap = PropertyUtil.getPropertyMap("/properties/erp.properties");

	private static final String URL = "http://218.2.197.230:8889";

	/**
	 * 导表到excel
	 * @param list 列表
	 * @param outputStream 输出流
	 */
	public static void exportExcel(List<Map<String, Object>> list, ServletOutputStream outputStream) {
		try {

			String[] titles = { "序号", "位置名称", "植物名称", "环境图片", "植物图片", "数量", "单价(元/天/盆)", "总价(元/天)" };
			// 1、创建工作簿
			XSSFWorkbook workbook = new XSSFWorkbook();
			// 看总共列数有多少 就合并多少列数
			Integer counttitle = list.get(0).size() - 1;
			// 1.1、创建合并单元格对象
			CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, titles.length - 1);// 起始行号，结束行号，起始列号，结束列号

			// 1.2、头标题样式
			XSSFCellStyle style1 = createCellStyle(workbook, (short) 16);

			// 1.3、列标题样式
			XSSFCellStyle style2 = createCellStyle(workbook, (short) 13);

			// 2、创建工作表
			XSSFSheet sheet = workbook.createSheet("花盆");
			// 2.1、加载合并单元格对象
			sheet.addMergedRegion(cellRangeAddress);
			// 设置默认列宽
			sheet.setDefaultColumnWidth(25);

			// 3、创建行
			// 3.1、创建头标题行；并且设置头标题
			XSSFRow row1 = sheet.createRow(0);
			XSSFCell cell1 = row1.createCell(0);
			// 加载单元格样式
			cell1.setCellStyle(style1);
			cell1.setCellValue("订单明细");

			// 3.2、创建列标题行；并且设置列标题
			XSSFRow row2 = sheet.createRow(1);

			String[] widths = { "4500", "8000", "8000", "8000", "8000", "3500", "6000", "6000" };
			for (int i = 0; i < titles.length; i++) {
				XSSFCell cell2 = row2.createCell(i);
				// 加载单元格样式
				// 设置每个单元格宽度
				cell2.getSheet().setColumnWidth(i, Integer.parseInt(widths[i].toString()));

				cell2.setCellStyle(style2);

				cell2.setCellValue(titles[i]);
			}
			// 合并列
			// sheet.addMergedRegion(new CellRangeAddress(5, (short)5, 5, (short)6));
			// 合并行
			// sheet.addMergedRegion(new CellRangeAddress(2, (short)3, 2, (short)2));

			// sheet.addMergedRegion(new CellRangeAddress(2, (short)3, 6, (short)6));
			// 4、操作单元格；将列表写入excel
			if (list != null) {

				for (int j = 0; j < list.size(); j++) {
					// XSSFCellStyle style = workbook.createCellStyle();
					// style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//水平居中
					// style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//上下居中
					List<Map<String, Object>> detail = JSONObject
							.parseObject(list.get(j).get("orderDetailList").toString(), ArrayList.class);
					Integer det = detail.size();
					// 看他下面的子集有多少

					if (det > 1) {
						Integer firstnum = Integer.valueOf(sheet.getLastRowNum()) - 1;
						Integer lastnum = (Integer.valueOf(sheet.getLastRowNum()) + det) - 2;
						/* System.out.println(firstnum+"++++"+lastnum); */

						// sheet.addMergedRegion(new CellRangeAddress(firstnum, (short)lastnum.shortValue(), 0,
						// (short)0));
						// sheet.addMergedRegion(new CellRangeAddress(firstnum, (short)lastnum.shortValue(), 1,
						// (short)1));
						// sheet.addMergedRegion(new CellRangeAddress(firstnum, (short)lastnum.shortValue(), 3,
						// (short)3));
					}

					for (int i = 0; i < det; i++) {
						XSSFCellStyle style = createCellStyle(workbook, (short) 18);
						style.setWrapText(true);
						XSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
						// 创建第一行
						XSSFCell cell11 = row.createCell(0);
						// 设置行高
						cell11.getRow().setHeightInPoints(120);
						cell11.setCellStyle(style);

						// CellRangeAddress region = new CellRangeAddress(5,6,2,5);
						// sheet.addMergedRegion(region);
						cell11.setCellValue(j + 1);
						// System.out.println(cell11.getColumnIndex());
						// addimg(sheet,workbook,"e:/1.jpg",(short)cell11.getColumnIndex(), (short)
						// (cell11.getColumnIndex()+1),j+2,j+3);
						// addimg(sheet,workbook,"e:/1.jpg",(short)cell11.getColumnIndex(), (short)
						// (cell11.getColumnIndex()+1),j+2,j+3);
						// 创建第二行

						XSSFCell cell12 = row.createCell(1);
						cell12.setCellStyle(style);
						cell12.setCellValue(list.get(j).get("storage_name").toString());

						XSSFCell cell13 = row.createCell(2);
						cell13.setCellStyle(style);
						cell13.setCellValue(detail.get(i).get("name").toString());

						// addimg(sheet,workbook,"e:/2.jpg",(short)cell13.getColumnIndex(), (short)
						// (cell13.getColumnIndex()+1),j+2,j+3);

						XSSFCell cell14 = row.createCell(3);
						if (i == 0) {
							// 获取图片主图
							String imgs = list.get(j).get("picture_id").toString();
							cell14.setCellStyle(style);
							// excel 的sheet
							// workbook EXCEL
							// imgs 图片路径

							addimg(sheet, workbook, imgs, (short) cell14.getColumnIndex(),
									(short) (cell14.getColumnIndex() + 1), sheet.getLastRowNum(),
									sheet.getLastRowNum() + 1);

						}

						// cell14.setCellValue(detail.get(i).get("name").toString());

						XSSFCell cell15 = row.createCell(4);
						cell15.setCellStyle(style);
						// 获取图片分图
						String imgdetail = detail.get(i).get("pic").toString();
						addimg(sheet, workbook, imgdetail, (short) cell15.getColumnIndex(),
								(short) (cell15.getColumnIndex() + 1), sheet.getLastRowNum(),
								sheet.getLastRowNum() + 1);
						// cell15.setCellValue(detail.get(i).get("name").toString());

						XSSFCell cell16 = row.createCell(5);
						cell16.setCellStyle(style);
						cell16.setCellValue(detail.get(i).get("num").toString());
						count = count + Double.valueOf(detail.get(i).get("num").toString());

						XSSFCell cell17 = row.createCell(6);
						cell17.setCellStyle(style);
						cell17.setCellValue(Double.valueOf(detail.get(i).get("order_price").toString()));

						XSSFCell cell18 = row.createCell(7);
						cell18.setCellStyle(style);
						cell18.setCellValue(Double.valueOf(detail.get(i).get("order_price").toString())
								* Double.valueOf(detail.get(i).get("num").toString()));
						sum_day = sum_day + Double.valueOf(detail.get(i).get("order_price").toString())
								* Double.valueOf(detail.get(i).get("num").toString());
					}

					if (det > 1) {

						Integer firstnum = (Integer.valueOf(sheet.getLastRowNum()) - det) + 1;
						Integer lastnum = (Integer.valueOf(sheet.getLastRowNum()));

						sheet.addMergedRegion(new CellRangeAddress(firstnum, lastnum.shortValue(), 0, (short) 0));
						sheet.addMergedRegion(new CellRangeAddress(firstnum, lastnum.shortValue(), 1, (short) 1));
						sheet.addMergedRegion(new CellRangeAddress(firstnum, lastnum.shortValue(), 3, (short) 3));
					}

				}
			}

			for (int i = 0; i < 3; i++) {
				// 合计盆
				DecimalFormat df = new DecimalFormat(".##");
				if (i == 0) {
					XSSFRow rowdel = sheet.createRow(sheet.getLastRowNum() + 1);
					XSSFCell cell11 = rowdel.createCell(0);
					cell11.setCellStyle(style2);

					cell11.setCellValue("合计(盆)");

					XSSFCell cell12 = rowdel.createCell(1);
					cell12.setCellStyle(style2);
					cell12.setCellValue(count);
				} else if (i == 1) {
					XSSFRow rowdel = sheet.createRow(sheet.getLastRowNum() + 1);
					XSSFCell cell11 = rowdel.createCell(0);
					cell11.setCellStyle(style2);
					cell11.setCellValue("合计(元/天)");
					XSSFCell cell12 = rowdel.createCell(1);
					cell12.setCellStyle(style2);
					String st = df.format(sum_day);
					cell12.setCellValue("单品:" + st);
				} else if (i == 2) {
					XSSFRow rowdel = sheet.createRow(sheet.getLastRowNum() + 1);
					XSSFCell cell11 = rowdel.createCell(0);
					cell11.setCellStyle(style2);
					cell11.setCellValue("合计(元/年)");
					XSSFCell cell12 = rowdel.createCell(1);
					cell12.setCellStyle(style2);
					String st = df.format(sum_day * 365);
					cell12.setCellValue(st);
				}

			}

			count = 0.00;
			sum_day = 0.00;
			// sheet.addMergedRegion(new CellRangeAddress(6, (short)7, 6, (short)6));
			// 合并单元格
			// addMergedRegion(sheet, 0, 0, sheet.getLastRowNum(),workbook);
			// 5、输出
			workbook.write(outputStream);
			outputStream.close();
			// workbook.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 创建单元格样式
	 * @param workbook 工作簿
	 * @param fontSize 字体大小
	 * @return 单元格样式
	 */
	public static XSSFCellStyle createCellStyle(XSSFWorkbook workbook, short fontSize) {
		XSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		// 创建字体
		XSSFFont font = workbook.createFont();
		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 加粗字体
		font.setFontHeightInPoints(fontSize);
		// 加载字体
		style.setFont(font);
		return style;
	}

	// /**
	// * 合并单元格
	// * @param sheet 要合并单元格的excel 的sheet
	// * @param cellLine 要合并的列
	// * @param startRow 要合并列的开始行
	// * @param endRow 要合并列的结束行
	// */
	// public void addMergedRegion(XSSFSheet sheet, int cellLine, int startRow, int endRow,XSSFWorkbook workBook){
	//
	// XSSFCellStyle style = workBook.createCellStyle(); // 样式对象
	// style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直居中
	// style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平
	// //获取第一行的数据,以便后面进行比较
	// String s_will = sheet.getRow(startRow).getCell(cellLine).getStringCellValue();
	//// int count = 0;
	// boolean flag = true;
	// int every_starRow =0;
	// for (int i = 1; i <= endRow; i++) { //从第二行开始查看 i=1
	// String s_current = sheet.getRow(i).getCell(0).getStringCellValue(); //当前单元格值
	// if(s_will.equals(s_current)) {//当前和上一行值比较
	// if(flag){
	// every_starRow = i-1;//记录开始行
	// }
	// s_will = s_current;
	// if(sheet.getRow(i).getCell(0).getStringCellValue().equals(
	// sheet.getRow(i+1).getCell(0).getStringCellValue())){ //当前和下一行值比较
	// //相同 则继续下一行比较
	// System.out.println("当前行和下一行相同，继续循环~~~");
	// flag=false;
	// }else{
	// //不相同则要进行合并记录当前行
	// flag=true;
	// sheet.addMergedRegion(new CellRangeAddress(every_starRow,i,cellLine,cellLine));
	// XSSFRow row = sheet.getRow(every_starRow);
	// String cellValueTemp = sheet.getRow(every_starRow).getCell(0).getStringCellValue();
	// XSSFCell cell = row.createCell(0);
	// cell.setCellValue(cellValueTemp); // 跨单元格显示的数据
	// cell.setCellStyle(style); // 样式
	// ; }
	// }else{
	// s_will = s_current;
	// }
	// }
	// }

	public static void addimg(XSSFSheet sheet, XSSFWorkbook workBook, String imgid, short cel1, short cel2,
			Integer row1, Integer row2) throws Exception {
		XSSFDrawing patriarch = sheet.createDrawingPatriarch();
		XSSFCellStyle style = workBook.createCellStyle(); // 样式对象
		// 图片编辑器
		/*
		 * FileOutputStream fileOut = null; BufferedImage bufferImg = null; ByteArrayOutputStream byteArrayOut = new
		 * ByteArrayOutputStream(); bufferImg = ImageIO.read(new File(imgid)); byte[] datas= resize(150,150,bufferImg);
		 * XSSFClientAnchor anchor = new XSSFClientAnchor(50, 0, 0, 0,(short) cel1, row1, (short) cel2, row2);
		 * patriarch.createPicture(anchor, workBook.addPicture(datas, HSSFWorkbook.PICTURE_TYPE_JPEG));
		 */

		// ImageIO.write(bufferImg, "jpg", byteArrayOut);
		//
		// XSSFDrawing patriarch = sheet.createDrawingPatriarch();
		// /**
		// dx1 - the x coordinate within the first cell.//定义了图片在第一个cell内的偏移x坐标，既左上角所在cell的偏移x坐标，一般可设0
		// dy1 - the y coordinate within the first cell.//定义了图片在第一个cell的偏移y坐标，既左上角所在cell的偏移y坐标，一般可设0
		// dx2 - the x coordinate within the second cell.//定义了图片在第二个cell的偏移x坐标，既右下角所在cell的偏移x坐标，一般可设0
		// dy2 - the y coordinate within the second cell.//定义了图片在第二个cell的偏移y坐标，既右下角所在cell的偏移y坐标，一般可设0
		// col1 - the column (0 based) of the first cell.//第一个cell所在列，既图片左上角所在列
		// row1 - the row (0 based) of the first cell.//图片左上角所在行
		// col2 - the column (0 based) of the second cell.//图片右下角所在列
		// row2 - the row (0 based) of the second cell.//图片右下角所在行
		// */
		// XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0,(short) cel1, row1, (short) cel2, row2);
		// //插入图片
		// patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut.toByteArray(),
		// HSSFWorkbook.PICTURE_TYPE_JPEG));

		if ("".equals(imgid) || null == imgid) {
			return;
		}
		String urls = URL;
		if (imgid.indexOf("[") != -1) {
			JSONArray sa = JSONArray.parseArray(imgid);
			imgid = urls + sa.get(0).toString();
		} else {
			imgid = urls + imgid;
		}

		String imgUrl = java.net.URLDecoder.decode(imgid, "utf-8");
		URL url = null;
		int doub = imgid.lastIndexOf(".");
		// System.out.println(doub);
		int xie = imgid.lastIndexOf("/");
		// System.out.println(xie);
		String jiequ = imgid.substring(xie + 1, doub);
		// System.out.println(jiequ);
		if (isContainChinese(jiequ)) {
			String url_f = java.net.URLEncoder.encode(jiequ, "utf-8");

			String newimgid = imgid.replace(jiequ, url_f);

			// System.out.println("解析中文"+url_f);
			// System.out.println(newimgid);

			url = new URL(newimgid);
		} else {
			url = new URL(imgid);

		}

		// 打开链接
		HttpURLConnection conn = (HttpURLConnection) url.openConnection();
		// 设置请求方式为"GET"
		// conn.setRequestMethod("POST");
		// conn.setRequestProperty("Accept","text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8");
		// conn.setRequestProperty("Accept-Encoding","gzip, deflate");
		// conn.setRequestProperty("Accept-Language","zh-CN,zh;q=0.9");
		// conn.setRequestProperty("Content-Type","text/plain; charset=utf-8");
		// conn.setRequestProperty("User-Agent","Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like
		// Gecko) Chrome/66.0.3359.117 Safari/537.36");
		// conn.setRequestProperty("Cache-Control","max-age=0");
		// 超时响应时间为5秒
		conn.setConnectTimeout(5 * 1000);
		// 通过输入流获取图片数据
		InputStream inStream = null;
		try {

			inStream = conn.getInputStream();
			// 得到图片的二进制数据，以二进制封装得到数据，具有通用性
			byte[] data = readInputStream(inStream);
			// 把二进制数据转换成图片
			ByteArrayInputStream bais = new ByteArrayInputStream(data);
			BufferedImage bi1 = ImageIO.read(bais);
			byte[] datas = resize(150, 150, bi1);

			XSSFClientAnchor anchor = new XSSFClientAnchor(50, 0, 0, 0, cel1, row1, cel2, row2);

			patriarch.createPicture(anchor, workBook.addPicture(datas, HSSFWorkbook.PICTURE_TYPE_JPEG));
		} catch (Exception e) {

		}

	}

	private static byte[] readInputStream(InputStream inStream) throws Exception {
		ByteArrayOutputStream outStream = new ByteArrayOutputStream();
		// 创建一个Buffer字符串
		byte[] buffer = new byte[1024];
		// 每次读取的字符串长度，如果为-1，代表全部读取完毕
		int len = 0;
		// 使用一个输入流从buffer里把数据读取出来
		while ((len = inStream.read(buffer)) != -1) {
			// 用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
			outStream.write(buffer, 0, len);

		}
		// 关闭输入流
		inStream.close();
		// 把outStream里的数据写入内存
		return outStream.toByteArray();
	}

	public static boolean isContainChinese(String str) {

		Pattern p = Pattern.compile("[\u4e00-\u9fa5]");
		Matcher m = p.matcher(str);
		if (m.find()) {
			return true;
		}
		return false;
	}

	public static byte[] resize(int w, int h, Image s) throws IOException {
		// SCALE_SMOOTH 的缩略算法 生成缩略图片的平滑度的 优先级比速度高 生成的图片质量比较好 但速度慢

		/**
		 * jpg 不能直接转png 否则会让白色背景失真。
		 * */

		// png图片
		BufferedImage image = new BufferedImage(w, h, BufferedImage.TYPE_INT_ARGB);

		// 白色底框没有了
		// BufferedImage image = new BufferedImage(w, h,BufferedImage.TYPE_INT_RGB );

		// 变暗
		// BufferedImage image = new BufferedImage(w, h,BufferedImage.TYPE_INT_ARGB_PRE );
		// 变暗
		// BufferedImage image = new BufferedImage(w, h,BufferedImage.TYPE_INT_BGR );

		image.getGraphics().drawImage(s, 0, 0, w, h, null); // 绘制缩小后的图
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		ImageIO.write(image, "png", baos);
		return baos.toByteArray();
	}
}
